package com.haiyou.data.common.persister.mysql.persister.page;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

import org.apache.commons.lang3.Validate;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import com.google.common.base.Stopwatch;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.haiyou.common.constants.ErrorCode;
import com.haiyou.common.entity.Entity;
import com.haiyou.common.util.check.Assert;
import com.haiyou.common.util.object.ClassUtils;
import com.haiyou.common.util.string.StringFormatUitls;
import com.haiyou.data.common.persister.mysql.annotation.Table;
import com.haiyou.data.common.persister.mysql.help.EntityHelper;
import com.haiyou.data.common.persister.mysql.help.TableHelper;
import com.haiyou.data.common.persister.mysql.persister.PreparedPersister;
import com.haiyou.data.common.persister.mysql.persister.condition.QueryCondition;
import com.haiyou.data.common.persister.mysql.persister.condition.QueryConditions;
import com.haiyou.data.common.persister.mysql.persister.roll.RollEntity;

import lombok.extern.slf4j.Slf4j;

/**
 * 分页查询 注意仅支持单表分页
 * 
 * @author xingyuan
 * @date 2023年8月28日 上午10:51:16
 */
@Slf4j
public abstract class PagePreparedPersister<Id extends Serializable & Comparable<Id>, E extends Entity<Id>>
		extends PreparedPersister<Id, E> {

	public static final String pageSql = "select * from `{}` as `a` INNER JOIN (select `id` from `{}` {} order by id limit {},{}) `b` on `a`.`id` = `b`.`id`;";

	public static final String pageSqlDesc = "select * from `{}` as `a` INNER JOIN (select `id` from `{}` {} order by id desc limit {},{}) `b` on `a`.`id` = `b`.`id`;";

	
	/**
	 * 条件查询count
	 */
	private static final String countSql = "select count(*) from `{}` {};";

	/**
	 * 分页条件
	 */
	private static final String where_format = " where {}";
	private static final String and = "and";

	/**
	 * 缓存统计
	 */
	private static final Map<String, PageCountCache> countCache = Maps.newConcurrentMap();

	@Override
	public void init() {
		super.init();
		// 检查一下是否是 单表逻辑 滚表需要走另一个
		Assert.isTrue(!ClassUtils.findAllClasses(this.getEntityClass()).contains(RollEntity.class),
				ErrorCode.UNKNOW_EXCEPTION, "rollEntity {} extends error class", this.getClass().getName());
	}

	/**
	 * 缓存统计
	 * 
	 * @return
	 */
	public int cacheCount() {
		String className = this.getEntityClass().getName();
		PageCountCache cache = countCache.compute(className, (k, v) -> {
			if (v == null) {
				v = new PageCountCache();
				v.setCount(this.count());
				v.setTime(System.currentTimeMillis());
				v.setClassName(className);
			}

			if (v.needUpdate()) {
				v.setCount(this.count());
				v.setTime(System.currentTimeMillis());
			}
			return v;
		});

		return cache.getCount();
	}

	/**
	 * 构建预编译条件
	 */
	public String conditionsPrepared(QueryConditions conditions, String option) {

		Validate.isTrue(option.toLowerCase().equals("and") || option.toLowerCase().equals("or"));

		StringBuilder builder = new StringBuilder();

		int i = 0;

		for (QueryCondition condition : conditions.getConditions()) {

			String column = condition.getColumn();

			switch (condition.getQueryType()) {
			// 默认逻辑比较查询
			case QueryCondition.query_type_compare: {
				builder.append('`').append(column).append('`').append(condition.getCompare()).append("?");
				break;
			}
			// 模糊查询
			case QueryCondition.query_type_like: {
				builder.append('`').append(column).append('`').append(" ").append(condition.getQueryType())
						.append(" concat('%',?,'%')");
				break;
			}
			// 直接条件查询
			case QueryCondition.query_type_condition: {
				if (StringUtils.hasLength(condition.getCondition())) {
					builder.append(condition.getCondition());
				}
				break;
			}
			// in
			case QueryCondition.query_type_in:{
					builder.append("`").append(column).append("` ").append(condition.getQueryType())
							.append("(");
				String value = (String) condition.getValue();
				String[] split = value.split(",");
				for (String s : split) {
					builder.append(Integer.parseInt(s)).append(",");
				}
				builder.deleteCharAt(builder.lastIndexOf(","));
				builder.append(")");
			}
			break;
			default:
				continue;
			}
			if (i++ < conditions.getConditions().size() - 1)
				builder.append(' ').append(option).append(' ');
		}
		return builder.toString();
	}

	/**
	 * 分页查询
	 * 
	 * @param page
	 * @param limit
	 * @return
	 */
	public List<E> queryPageByCondition(int page, int limit) {
		return queryPageByCondition(page, limit, null);
	}

	/**
	 * 分页查询 <br>
	 * 注意次方法仅支持单表,分表情况请重写此方法
	 * 
	 * @param page      页数 从1开始
	 * @param limit     单页数量
	 * @param condition 查询条件 key 字段名 value 字段值
	 * @return
	 */
	public List<E> queryPageByCondition(int page, int limit, QueryConditions condition) {

		List<E> result = Lists.newArrayList();

		// 一般不需要分库
		for (String db : databaseMeta.getReallyNames()) {
			// 需要分表 需要一个排序逻辑
			for (String tab : TableHelper.allTables(tableMeta)) {
				List<E> list = queryPageByCondition(db, tab, page, limit, condition);
				result.addAll(list);
			}
		}

		return result;
	}

	
	/**
	 * 基于条件的统计查询
	 * 
	 * @param db
	 * @param tab
	 * @param condition
	 * @return
	 */
	public long queryCountByCondition(String db, String tab, QueryConditions condition) {

		String sql;

		if (!ObjectUtils.isEmpty(condition.getConditions())) {
			String conditionsSql = conditionsPrepared(condition, and);
			// 存在条件参数
			conditionsSql = StringFormatUitls.format(where_format, conditionsSql);
			sql = StringFormatUitls.format(countSql, tab, conditionsSql);
		} else {
			sql = StringFormatUitls.format(countSql, tab, "");
		}

		if (log.isDebugEnabled()) {
			log.debug("===========执行的SQL==========");
			log.debug(sql);
			log.debug("==============================");
		}

		//log.info("sql {}", sql);
		
		// 预编译查询
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		Stopwatch stopwatch = Stopwatch.createStarted();

		try {

			connection = on(db);
			preparedStatement = connection.prepareStatement(sql);
			
			// 组装预编译SQL
			if (!ObjectUtils.isEmpty(condition)) {
				int parameterIndex = 1;
				for (QueryCondition queryCondition : condition.getConditions()) {
					if (!QueryCondition.column_type.contains(queryCondition.getQueryType())) {
						continue;
					}
					Object value = queryCondition.getValue();
					//log.info("设置值 {} {}",parameterIndex,value);
					preparedStatement.setObject(parameterIndex++, value);
				}
			}

			// 查询结果解析
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null) {
				ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
				while (resultSet != null && resultSet.next()) {
					for (int i = 0; ++i <= resultSetMetaData.getColumnCount();) {
						String name = resultSetMetaData.getColumnLabel(i);
						Object value = resultSet.getObject(name);
						return (long)value;
					}
				}
			}

		} catch (Exception e) {
			throw new RuntimeException(e.getMessage() + ":" + sql, e);
		} finally {
			off(null, preparedStatement, connection);
			long elapsed = stopwatch.elapsed(TimeUnit.MILLISECONDS);
			if (elapsed > executeSlowly())
				log.warn("SLOWLY: [{}] elapsed [{} ms]", sql, elapsed);
			else {
				if (log.isDebugEnabled()) {
					log.debug("[{}] elapsed [{} ms],", sql, elapsed);
				}
			}
		}
	
		return 0l;
	}

	public List<E> queryPageByCondition(String db, String tab, int page, int limit, QueryConditions condition) {
		return queryPageByCondition(db, tab, page, limit, pageSqlDesc, condition);
	}
	
	
	public List<E> queryPageDescByCondition(String db, String tab, int page, int limit, QueryConditions condition) {
		return queryPageByCondition(db, tab, page, limit, pageSqlDesc, condition);
	}
	
	/**
	 * 基于偏移量进行分页查询
	 * @param db
	 * @param tab
	 * @param offset 
	 * @param pageSize
	 * @param pageSql
	 * @param condition
	 * @return
	 */
	public List<E> queryPageByOffsetAndCondition(String db, String tab, int offset, int pageSize, 
			String pageSql,
			QueryConditions condition) {
		
		Table table = this.getEntityClass().getAnnotation(Table.class);

		Assert.isTrue(table != null, ErrorCode.UNKNOW_EXCEPTION, "查询实体类 {} 需要 配置 @Table",
				this.getEntityClass().getSimpleName());
		
		int value1 = offset;
		int value2 = pageSize;
		
		String sql;

		if (!ObjectUtils.isEmpty(condition.getConditions())) {
			String conditionsSql = conditionsPrepared(condition, and);
			// 存在条件参数
			conditionsSql = StringFormatUitls.format(where_format, conditionsSql);
			sql = StringFormatUitls.format(pageSql,tab, tab, conditionsSql, value1, value2);
		} else {
			// 没有条件参数
			sql = StringFormatUitls.format(pageSql, tab, tab, "", value1, value2);
		}

		if (log.isDebugEnabled()) {
			log.debug("===========执行的SQL==========");
			log.debug(sql);
			log.debug("==============================");
		}
		
		//log.info(sql);
		
		// 预编译查询
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		Stopwatch stopwatch = Stopwatch.createStarted();

		try {

			connection = on(db);
			preparedStatement = connection.prepareStatement(sql);

			// 组装预编译SQL
			if (!ObjectUtils.isEmpty(condition)) {
				int parameterIndex = 1;
				for (QueryCondition queryCondition : condition.getConditions()) {
					if (!QueryCondition.column_type.contains(queryCondition.getQueryType())) {
						continue;
					}
					Object value = queryCondition.getValue();
					preparedStatement.setObject(parameterIndex++, value);
				}
			}

			// 查询结果解析
			List<Map<String, Object>> list = Lists.newArrayList();
			ResultSet resultSet = preparedStatement.executeQuery();
			if (resultSet != null) {
				ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
				while (resultSet != null && resultSet.next()) {
					Map<String, Object> map = Maps.newLinkedHashMap();
					for (int i = 0; ++i <= resultSetMetaData.getColumnCount();) {
						String name = resultSetMetaData.getColumnLabel(i);
						Object value = resultSet.getObject(name);
						map.put(name, value);
					}
					list.add(map);
				}
			}

			List<E> result = Lists.newArrayList();
			for (Map<String, Object> map : list) {
				E entity = (E) EntityHelper.wrapped(entityClass, map, tableMeta);
				result.add(entity);
			}

			return result;

		} catch (Exception e) {
			throw new RuntimeException(e.getMessage() + ":" + sql, e);
		} finally {
			off(null, preparedStatement, connection);
			long elapsed = stopwatch.elapsed(TimeUnit.MILLISECONDS);
			if (elapsed > executeSlowly())
				log.warn("SLOWLY: [{}] elapsed [{} ms], offset={},pageSize={}", sql, elapsed, offset, pageSize);
			else {
				if (log.isDebugEnabled()) {
					log.debug("[{}] elapsed [{} ms], offset={},pageSize={}", sql, elapsed, offset, pageSize);
				}
			}
		}
		
	}
	
	/**
	 * 
	 * 条件分页查询
	 * 
	 * @param db
	 * @param tab
	 * @param page
	 * @param limit
	 * @param condition
	 * @return
	 */
	public List<E> queryPageByCondition(String db, String tab, int page, int limit, 
			String pageSql,
			QueryConditions condition) {

		page = page - 1;
		page = Math.max(page, 0);

		// 缓存统计
		int maxCount = this.cacheCount();
		int add = maxCount % limit > 0 ? 1 : 0;
		// 最大页面值
		int maxPage = maxCount / limit + add;
		
		page = Math.min(page, maxPage);
		
		int value1 = page * limit;
		int value2 = limit;

		return queryPageByOffsetAndCondition(db, tab, value1, value2, pageSql, condition);
	}

}
